package beans;
import java.util.*;
import java.sql.*;
import java.io.*; 

/**
 * Add new component to the database, also if the manufacturer of adding component
 * is not in the DB, it also adds it to DB
 * @author Elena
 */
public class AddComponentBean {
    
    private String url=null;

    public AddComponentBean(String _url) {
        url=_url;
    }

    /**  
     * Add new component to the database, 
     * if it already exists - just change the amount of it,
     * also if the manufacturer of adding component
     * is not in the DB, it also adds it to DB
     *@param ac - AddComponentInsertBean class
     */
    public void AddComponent(AddComponentInsertBean ac) throws Exception{
        Connection conn =null;
        Connection conn2 =null;
        Connection conn3 =null;
        Connection conn4 =null;
        Statement stmt = null;
        Statement stmt2 = null;
        Statement stmt3 = null;
        Statement stmt4 = null;
        Statement stmt5 = null;
        int rs,rd;
        String man_id;
        boolean rid;

        try{
            Class.forName("com.mysql.jdbc.Driver");
            conn=DriverManager.getConnection(url);
            conn2=DriverManager.getConnection(url);
            conn3=DriverManager.getConnection(url);
            conn4=DriverManager.getConnection(url);
            
            stmt = conn.createStatement();
            stmt2 = conn2.createStatement();
            stmt3 = conn3.createStatement();
            stmt4 = conn3.createStatement();
            stmt5 = conn4.createStatement();
            
            String sql,sql_country,sql_man_insert,sql_getid,sql_hdd,sql_ram,sql_proc,sql_vc,sql_mb;
            
            conn.setAutoCommit(false);
            conn2.setAutoCommit(false);
            conn3.setAutoCommit(false);
            conn4.setAutoCommit(false);
            
            //look if a manufacturer for adding component already exists
            sql_country = "SELECT ID_MANUFACTURER,MANUFACTURER_NAME FROM MANUFACTURER  where ";
            sql_country+="MANUFACTURER_NAME="+"'"+ac.getCountry()+"'";
            
           ResultSet hasResult = stmt2.executeQuery(sql_country);
                      
           conn2.commit();
           
                     
           //if hasResult then update, else insert
           if (hasResult.next()) {
               man_id = hasResult.getString(1);
           } 
           else 
           {
               sql_man_insert="INSERT INTO MANUFACTURER (MANUFACTURER_NAME) VALUES ('"+ac.getCountry()+"')";
               rd=stmt3.executeUpdate(sql_man_insert);
               sql_getid = "SELECT MAX(ID_MANUFACTURER) FROM MANUFACTURER";
               rid = stmt4.execute(sql_getid);
               ResultSet rr = stmt4.getResultSet();
               rr.next();
               man_id = rr.getString(1);
            }
           conn3.commit();
                     
           //if component exists then update it, else add new one
            sql="";
           if (ac.getProduct().equalsIgnoreCase("link2")) {
               sql_hdd = "SELECT ID_HDD,NAME_HDD FROM HDD WHERE NAME_HDD="+"'"+ac.getName()+"'";
               
               ResultSet hasHdd = stmt5.executeQuery(sql_hdd);
               conn4.commit();
               
               if (hasHdd.next()) {
                   sql = "UPDATE HDD SET PRICE='"+ac.getPrice()+"',count=count+'"+ac.getCount()+"' WHERE NAME_HDD='"+ac.getName()+"'";
               }
               else {
            sql ="INSERT INTO HDD ( NAME_HDD,Price,count,id_man)";
	    sql += " VALUES ('" + ac.getName()+"',"+"'"+ ac.getPrice()+"'," + "'"+ ac.getCount()+"'"+",'"+man_id+"')";
    }       }
           else 
           {  if (ac.getProduct().equalsIgnoreCase("link1")){
                sql_ram = "SELECT ID_RAM,NAME_RAM FROM RAM WHERE NAME_RAM="+"'"+ac.getName()+"'";
                ResultSet hasHdd = stmt5.executeQuery(sql_ram);
                conn4.commit();
               if (hasHdd.next()) {
                   sql = "UPDATE RAM SET PRICE='"+ac.getPrice()+"',count='"+ac.getCount()+"' WHERE NAME_RAM='"+ac.getName()+"'";
               }
               else {
               sql ="INSERT INTO RAM ( NAME_RAM,Price,count,id_man)";
	    sql += " VALUES ('" + ac.getName()+"',"+"'"+ ac.getPrice()+"'," + "'"+ ac.getCount()+"'"+",'"+man_id+"')";
           }}
           else 
           { if (ac.getProduct().equalsIgnoreCase("link3")) {
                 sql_proc = "SELECT ID_PROC,NAME_PROC FROM PROCESSOR WHERE NAME_PROC="+"'"+ac.getName()+"'";
                ResultSet hasHdd = stmt5.executeQuery(sql_proc);
                conn4.commit();
               if (hasHdd.next()) {
               sql = "UPDATE PROCESSOR SET PRICE='"+ac.getPrice()+"',count='"+ac.getCount()+"' WHERE NAME_PROC='"+ac.getName()+"'";
                   }
               else {
                    sql ="INSERT INTO PROCESSOR ( NAME_PROC,Price,count,id_man)";
	    sql += " VALUES ('" + ac.getName()+"',"+"'"+ ac.getPrice()+"'," + "'"+ ac.getCount()+"'"+",'"+man_id+"')";
               } }
           else 
           {if (ac.getProduct().equalsIgnoreCase("link4")) {
               sql_vc = "SELECT ID_VC,NAME_VC FROM VIDEOCARD WHERE NAME_VC="+"'"+ac.getName()+"'";
                ResultSet hasHdd = stmt5.executeQuery(sql_vc);
                conn4.commit();
               if (hasHdd.next()) {
                  sql = "UPDATE VIDEOCARD SET PRICE='"+ac.getPrice()+"',count='"+ac.getCount()+"' WHERE NAME_VC='"+ac.getName()+"'";
                    }
               else {
               sql ="INSERT INTO VIDEOCARD ( NAME_VC,Price,count,id_man)";
	    sql += " VALUES ('" + ac.getName()+"',"+"'"+ ac.getPrice()+"'," + "'"+ ac.getCount()+"'"+",'"+man_id+"')";;
           } }
           else {
                sql_mb = "SELECT ID_MB,NAME_MB FROM MOTHERBOARD WHERE NAME_MB="+"'"+ac.getName()+"'";
                ResultSet hasHdd = stmt5.executeQuery(sql_mb);
                conn4.commit();
               if (hasHdd.next()) {
                     sql = "UPDATE MOTHERBOARD SET PRICE='"+ac.getPrice()+"',count='"+ac.getCount()+"' WHERE NAME_MB='"+ac.getName()+"'";
                  }
               else {
               sql ="INSERT INTO MOTHERBOARD ( NAME_MB,Price,count,id_man)";
	    sql += " VALUES ('" + ac.getName()+"',"+"'"+ ac.getPrice()+"'," + "'"+ ac.getCount()+"'"+",'"+man_id+"')";
           }}}}}
            rs = stmt.executeUpdate(sql);

            conn.commit();
	}
	catch(SQLException sqle){
            conn.rollback();
            throw new Exception(sqle);
	}
        finally {
	    try{
		stmt.close();
            }
	    catch(Exception e) {}
            try {
		conn.close();
            }
            catch(Exception e){}
        }
    }
}
